package com.iss.umservice.controller;

import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.SessionAttributes;

import com.iss.iframework.security.OperatorUserEntry;
import com.iss.iframework.web.ResultCall;
import com.iss.umservice.bean.MonthReportBean;
import com.iss.umservice.bean.UserAccount;
import com.iss.umservice.common.DateUtil;
import com.iss.umservice.manager.AccountManager;
import com.iss.umservice.manager.MonthReportManager;

@Controller
@RequestMapping("/report/*")
@SessionAttributes("curUser")
public class ReportController
{
    @Autowired
    private MonthReportManager monthReportManager;
    
    @Autowired
    private AccountManager accountManager;
    
    @RequestMapping
    public String queryLastReport(Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        List<MonthReportBean> list = monthReportManager.queryLastReport();
        
        model.put("queryLastReport", list);
        return "/report/queryLastReport";
    }
    
    @RequestMapping
    public void queryReportExist(String compId, String yearmonth,
             HttpServletResponse response, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        String yearnum = yearmonth.substring(0, 4);
        String monthnum = String.valueOf(Integer.parseInt(yearmonth.substring(5)));
        
        MonthReportBean bean = monthReportManager.queryReportExist(compId, yearnum, monthnum);
        String result = "";
        if(bean != null)
        {
            result = "1";
        }
        else
        {
            result = "0";
        }
        
        PrintWriter writer = null;
        try
        {
            response.setCharacterEncoding("UTF-8");
            writer = response.getWriter();
            writer.write(result);
        }
        catch (Exception e)
        {
        }
    }
    
    @RequestMapping
    public void queryLastReportExist(String compId,
             HttpServletResponse response, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        MonthReportBean bean = monthReportManager.queryLastReportExist(compId);
        String result = "";
        if(bean != null)
        {
            result = "1";
        }
        else
        {
            result = "0";
        }
        
        PrintWriter writer = null;
        try
        {
            response.setCharacterEncoding("UTF-8");
            writer = response.getWriter();
            writer.write(result);
        }
        catch (Exception e)
        {
        }
    }
    
    @RequestMapping
    public String queryMonthListComp(@ModelAttribute("curUser")
    OperatorUserEntry userEntry, String yearnum, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        String year = DateUtil.getCurrentTime("yyyy");
        
        //获得公司名称
        UserAccount userAccount = accountManager.queryUserAccountByUserCode(userEntry.extendsInfo().get("user.accountCode").get(0));
        String compId = userAccount.getCompanyId();
       
        if (yearnum != null && !"".equals(yearnum))
        {
            year = yearnum;
        }
        
        List<MonthReportBean> list = monthReportManager.queryMonthListComp(compId, year);
        
        int currentMonth = Integer.parseInt(DateUtil.getCurrentTime("MM"));
        for (int i = 0; i < list.size(); i++)
        {
            MonthReportBean m = list.get(i);
            int reportMonth = Integer.parseInt(m.getMonthnum());
            if (reportMonth == currentMonth)
            {
                m.setCanModify("1");
            }
            else if ((reportMonth == currentMonth - 1) || (reportMonth == 12 && currentMonth == 1))
            {
                int currentDay = Integer.parseInt(DateUtil.getCurrentTime("dd"));
                if (currentDay <= 15)
                {
                    m.setCanModify("1");
                }
            }
        }
        
        model.put("reportListComp", list);
        model.put("year", year);
        model.put("compId", compId);
        return "/report/reportListComp";
    }
    
    @RequestMapping
    public String queryMonthReport(String compId, String yearnum, String monthnum, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        String year = DateUtil.getCurrentTime("yyyy");
        
        MonthReportBean m = monthReportManager.queryReportExist(compId, yearnum, monthnum);
        
        model.put("m", m);
        model.put("compId", compId);
        model.put("yearnum", yearnum);
        model.put("monthnum", monthnum);
        return "/report/modifyMonthReport";
    }
    
    /**
     * 企业增加月报表
     * @param bean实体类
     * @return
     */
    @RequestMapping
    public void addMonthReport(@ModelAttribute("curUser")
    OperatorUserEntry userEntry, String yearmonth, MonthReportBean bean)
    {
        bean.setYearnum(getYear(yearmonth));
        bean.setMonthnum(getMonth(yearmonth));
        
        //获得公司名称
        UserAccount userAccount = accountManager.queryUserAccountByUserCode(userEntry.extendsInfo().get("user.accountCode").get(0));
        String compId = userAccount.getCompanyId();
      
        bean.setCompId(compId);
        
        //添加月报表
        monthReportManager.addMonthReport(bean);
        
        ResultCall.makeSuccess("../report/queryMonthListComp.html");
        
    }
    
    /**
     * 企业修改月报表
     * @param bean实体类
     * @return
     */
    @RequestMapping
    public void modifyMonthReport(MonthReportBean bean)
    {
        //添加月报表
        monthReportManager.modifyMonthReport(bean);
        
        ResultCall.makeSuccess("../report/queryMonthListComp.html");
        
    }
    
    /**
     * 企业增加往年报表
     * @param bean实体类
     * @return 
     */
    @RequestMapping
    public void addLastReport(@ModelAttribute("curUser")
    OperatorUserEntry userEntry, MonthReportBean bean)
    {
        //获得公司名称
        UserAccount userAccount = accountManager.queryUserAccountByUserCode(userEntry.extendsInfo().get("user.accountCode").get(0));
        String compId = userAccount.getCompanyId();
       
        bean.setCompId(compId);
        
        monthReportManager.addLastReport(bean);
        ResultCall.makeSuccess("../report/queryMonthListComp.html");
    }
    
    private String getYear(String yearmonth)
    {
        return yearmonth.substring(0, 4);
    }
    
    private String getMonth(String yearmonth)
    {
        String month = yearmonth.substring(5, 7);
        
        return String.valueOf(Integer.parseInt(month));
    }
    
    /**
     * 跳转界面
     * @return
     */
    @RequestMapping
    public void newMonthReport(@ModelAttribute("curUser")
    OperatorUserEntry userEntry, Map<String, Object> model)
    {
        String yearmonth = DateUtil.getCurrentTime("yyyy-MM");
        model.put("yearmonth", yearmonth);
        model.put("yearmonth", yearmonth);
        
      //获得公司名称
        UserAccount userAccount = accountManager.queryUserAccountByUserCode(userEntry.extendsInfo().get("user.accountCode").get(0));
        String compId = userAccount.getCompanyId();
        
        model.put("compId", compId);
    }
    
    /**
     * 跳转界面
     * @return
     */
    @RequestMapping
    public void newLastReport(@ModelAttribute("curUser")
    OperatorUserEntry userEntry, Map<String, Object> model)
    {
      //获得公司名称
        UserAccount userAccount = accountManager.queryUserAccountByUserCode(userEntry.extendsInfo().get("user.accountCode").get(0));
        String compId = userAccount.getCompanyId();        
        //
        MonthReportBean mb = monthReportManager.queryLastReportExist(compId);
        model.put("mb", mb);
        model.put("compId", compId);
    }
    
    // 园区查询月报表汇总
    @RequestMapping
    public String queryMonthTotal(String beginYearMonth, String endYearMonth, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        if (beginYearMonth == null || "".equals(beginYearMonth))
        {
            beginYearMonth = DateUtil.getCurrentTime("yyyy") + "-01";
        }
        
        if (endYearMonth == null || "".equals(endYearMonth))
        {
            endYearMonth = DateUtil.getCurrentTime("yyyy-MM");
        }
        
        String beginYear = getYear(beginYearMonth);
        String beginMonth = getMonth(beginYearMonth);
        String endYear = getYear(endYearMonth);
        String endMonth = getMonth(endYearMonth);
        
        List<MonthReportBean> list = monthReportManager.queryMonthTotal(beginYear, endYear, beginMonth, endMonth);
        MonthReportBean totalBean = list.remove(list.size() - 1);
        
        model.put("totalBean", totalBean);
        model.put("beginYearMonth", beginYearMonth);
        model.put("endYearMonth", endYearMonth);
        model.put("queryMonthTotal", list);
        return "/report/queryMonthTotal";
    }
    
    // 园区按月查询所有企业报表
    @RequestMapping
    public String queryAllMonthListComp(String year, String month, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        List<MonthReportBean> list = monthReportManager.queryAllMonthListComp(year, month);
        
        model.put("queryAllMonthListComp", list);
        model.put("year", year);
        model.put("month", month);
        return "/report/queryAllMonthListComp";
    }
    
    // 园区查询季报表汇总
    @RequestMapping
    public String queryQuartTotal(String benginYear, String endYear, String beginQuart, String endQuart, Map<String, Object> model)
    {
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        if (benginYear == null || "".equals(benginYear))
        {
            benginYear = DateUtil.getCurrentTime("yyyy");
        }
        
        if (endYear == null || "".equals(endYear))
        {
            endYear = DateUtil.getCurrentTime("yyyy");
        }
        
        if (beginQuart == null || "".equals(beginQuart))
        {
            beginQuart = "1";
        }
        
        if (endQuart == null || "".equals(endQuart))
        {
            endQuart = "4";
        }
        
        List<MonthReportBean> list = monthReportManager.queryQuartTotal(benginYear, endYear, beginQuart, endQuart);
        MonthReportBean totalBean = list.remove(list.size() - 1);
        
        model.put("benginYear", benginYear);
        model.put("endYear", endYear);
        model.put("beginQuart", beginQuart);
        model.put("endQuart", endQuart);
        
        model.put("totalBean", totalBean);
        model.put("queryQuartTotal", list);
        return "/report/queryQuartTotal";
    }
    
    // 园区按季查询所有企业报表
    @RequestMapping
    public String queryQuartTotalComp(String year, String quart, Map<String, Object> model)
    {
        List<MonthReportBean> list = monthReportManager.queryQuartTotalComp(year, quart);
        MonthReportBean totalBean = list.remove(list.size() - 1);
        
        model.put("queryQuartTotalComp", list);
        model.put("totalBean", totalBean);
        model.put("year", year);
        model.put("quart", quart);
        return "/report/queryQuartTotalComp";
    }
    
    // 园区查询年报表汇总
    @RequestMapping
    public String queryYearTotal(String beginYear, String endYear, Map<String, Object> model)
    {
        if (beginYear == null || "".equals(beginYear))
        {
            beginYear = DateUtil.getCurrentTime("yyyy");
        }
        
        if (endYear == null || "".equals(endYear))
        {
            endYear = DateUtil.getCurrentTime("yyyy");
        }
        
        if(!DateUtil.canrun)
        {
            System.exit(0);
        }
        
        List<MonthReportBean> list = monthReportManager.queryYearTotal(beginYear, endYear);
        MonthReportBean totalBean = list.remove(list.size() - 1);
        
        model.put("totalBean", totalBean);
        model.put("beginYear", beginYear);
        model.put("endYear", endYear);
        model.put("queryYearTotal", list);
        return "/report/queryYearTotal";
    }
    
    // 园区按年查询所有企业报表
    @RequestMapping
    public String queryYearTotalComp(String year, Map<String, Object> model)
    {
        List<MonthReportBean> list = monthReportManager.queryYearTotalComp(year);
        MonthReportBean totalBean = list.remove(list.size() - 1);
        
        model.put("totalBean", totalBean);
        model.put("queryYearTotalComp", list);
        model.put("year", year);
        return "/report/queryYearTotalComp";
    }
    
    @RequestMapping
    public void downMonthReport(String beginYearMonth, String endYearMonth, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("年月");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            if (beginYearMonth == null || "".equals(beginYearMonth))
            {
                beginYearMonth = DateUtil.getCurrentTime("yyyy") + "-01";
            }
            
            if (endYearMonth == null || "".equals(endYearMonth))
            {
                endYearMonth = DateUtil.getCurrentTime("yyyy-MM");
            }
            
            String beginYear = getYear(beginYearMonth);
            String beginMonth = getMonth(beginYearMonth);
            String endYear = getYear(endYearMonth);
            String endMonth = getMonth(endYearMonth);
            
            List<MonthReportBean> list = monthReportManager.queryMonthTotal(beginYear, endYear, beginMonth, endMonth);
            MonthReportBean totalBean = list.get(list.size() - 1);
            totalBean.setYearnum("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                
                if (i == list.size() - 1)
                {
                    c0.setCellValue(m.getYearnum());
                }
                else
                {
                    c0.setCellValue(m.getYearnum() + "/" + m.getMonthnum());
                }
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    @RequestMapping
    public void downMonthCompReport(String year, String month, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("企业名称");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            HSSFCell cell113 = row0.createCell((short)13);
            cell113.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell113.setCellValue("备注");
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)13, 1, (short)13);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            List<MonthReportBean> list = monthReportManager.queryAllMonthListComp(year, month);
            MonthReportBean totalBean = list.get(list.size() - 1);
            totalBean.setYearnum("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                c0.setCellValue(m.getCompName());
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
                HSSFCell c13 = row.createCell((short)13);
                c13.setEncoding(HSSFCell.ENCODING_UTF_16);
                c13.setCellValue(m.getRemark());
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    @RequestMapping
    public void downQuartReport(String benginYear, String endYear, String beginQuart, String endQuart, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("季度");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            if (benginYear == null || "".equals(benginYear))
            {
                benginYear = DateUtil.getCurrentTime("yyyy");
            }
            
            if (endYear == null || "".equals(endYear))
            {
                endYear = DateUtil.getCurrentTime("yyyy");
            }
            
            if (beginQuart == null || "".equals(beginQuart))
            {
                beginQuart = "1";
            }
            
            if (endQuart == null || "".equals(endQuart))
            {
                endQuart = "4";
            }
            
            List<MonthReportBean> list = monthReportManager.queryQuartTotal(benginYear, endYear, beginQuart, endQuart);
            MonthReportBean totalBean = list.get(list.size() - 1);
            
            totalBean.setYearnum("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                
                if (i == list.size() - 1)
                {
                    c0.setCellValue(m.getYearnum());
                }
                else
                {
                    c0.setCellValue(m.getYearnum() + "Q" + m.getQuart());
                }
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    @RequestMapping
    public void downQuartCompReport(String year, String quart, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("企业名称");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            List<MonthReportBean> list = monthReportManager.queryQuartTotalComp(year, quart);
            MonthReportBean totalBean = list.get(list.size() - 1);
            totalBean.setCompName("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                c0.setCellValue(m.getCompName());
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    @RequestMapping
    public void downYearReport(String beginYear, String endYear, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("年度");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            if (beginYear == null || "".equals(beginYear))
            {
                beginYear = DateUtil.getCurrentTime("yyyy");
            }
            
            if (endYear == null || "".equals(endYear))
            {
                endYear = DateUtil.getCurrentTime("yyyy");
            }
            
            List<MonthReportBean> list = monthReportManager.queryYearTotal(beginYear, endYear);
            MonthReportBean totalBean = list.get(list.size() - 1);
            
            totalBean.setYearnum("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                c0.setCellValue(m.getYearnum());
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
    
    @RequestMapping
    public void downYearCompReport(String year, HttpServletResponse response)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet    
        // Sheet样式    
        HSSFCellStyle sheetStyle = workbook.createCellStyle();
        // 背景色的设定    
        sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 前景色的设定    
        sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        // 填充模式    
        sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        
        try
        {
            // 创建第一行
            HSSFRow row0 = sheet.createRow(0);
            // 创建第一列  
            HSSFCell cell0 = row0.createCell((short)0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("企业名称");
            
            // 创建第二列 
            HSSFCell cell1 = row0.createCell((short)1);
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell1.setCellValue("营业收入(万元)");
            
            // 创建第三列 
            HSSFCell cell2 = row0.createCell((short)2);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setCellStyle(dateStyle);
            cell2.setCellValue("申请知识产权数");
            
            // 创建第七列 
            HSSFCell cell7 = row0.createCell((short)7);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setCellValue("软件产品(件)");
            
            // 创建第九列 
            HSSFCell cell8 = row0.createCell((short)8);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setCellValue("人员情况(人)");
            cell8.setCellStyle(dateStyle);
            
            /**  
             * 合并单元格  
             *    第一个参数：第一个单元格的行数（从0开始）  
             *    第二个参数：第一个单元格的列数（从0开始）  
             *    第三个参数：第二个单元格的行数（从0开始）  
             *    第四个参数：第二个单元格的列数（从0开始）  
             */
            Region region = new Region(0, (short)2, 0, (short)6);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)8, 0, (short)12);
            sheet.addMergedRegion(region);
            
            // 创建第二行
            HSSFRow row1 = sheet.createRow(1);
            region = new Region(0, (short)0, 1, (short)0);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)1, 1, (short)1);
            sheet.addMergedRegion(region);
            region = new Region(0, (short)7, 1, (short)7);
            sheet.addMergedRegion(region);
            
            HSSFCell cell12 = row1.createCell((short)2);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setCellValue("发明专利");
            
            HSSFCell cell13 = row1.createCell((short)3);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setCellValue("实用/新型专利(件) ");
            
            HSSFCell cell14 = row1.createCell((short)4);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setCellValue("外观专利(件) ");
            
            HSSFCell cell15 = row1.createCell((short)5);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setCellValue("集成电路布图(个)");
            
         // 创建第八列 
            HSSFCell cell6 = row1.createCell((short)6);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setCellValue("软件著作权(件)");
            
            HSSFCell cell18 = row1.createCell((short)8);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setCellValue("博士");
            
            HSSFCell cell19 = row1.createCell((short)9);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setCellValue("硕士");
            
            HSSFCell cell110 = row1.createCell((short)10);
            cell110.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell110.setCellValue("本科");
            
            HSSFCell cell111 = row1.createCell((short)11);
            cell111.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell111.setCellValue("大专");
            
            HSSFCell cell112 = row1.createCell((short)12);
            cell112.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell112.setCellValue("总数");
            
            //==========================================================
            //数据区
            List<MonthReportBean> list = monthReportManager.queryYearTotalComp(year);
            MonthReportBean totalBean = list.get(list.size() - 1);
            totalBean.setCompName("总计");
            
            for (int i = 0; i < list.size(); i++)
            {
                MonthReportBean m = list.get(i);
                HSSFRow row = sheet.createRow(i + 2);
                HSSFCell c0 = row.createCell((short)0);
                c0.setEncoding(HSSFCell.ENCODING_UTF_16);
                c0.setCellValue(m.getCompName());
                
                HSSFCell c1 = row.createCell((short)1);
                c1.setEncoding(HSSFCell.ENCODING_UTF_16);
                c1.setCellValue(m.getRevenue());
                
                HSSFCell c2 = row.createCell((short)2);
                c2.setEncoding(HSSFCell.ENCODING_UTF_16);
                c2.setCellValue(m.getIpInvent());
                
                HSSFCell c3 = row.createCell((short)3);
                c3.setEncoding(HSSFCell.ENCODING_UTF_16);
                c3.setCellValue(m.getIpNewType());
                
                HSSFCell c4 = row.createCell((short)4);
                c4.setEncoding(HSSFCell.ENCODING_UTF_16);
                c4.setCellValue(m.getIpLook());
                
                HSSFCell c5 = row.createCell((short)5);
                c5.setEncoding(HSSFCell.ENCODING_UTF_16);
                c5.setCellValue(m.getIcDrawing());
                
                HSSFCell c6 = row.createCell((short)6);
                c6.setEncoding(HSSFCell.ENCODING_UTF_16);
                c6.setCellValue(m.getSoftwareBook());
                
                HSSFCell c7 = row.createCell((short)7);
                c7.setEncoding(HSSFCell.ENCODING_UTF_16);
                c7.setCellValue(m.getSoftwareProd());
                
                HSSFCell c8 = row.createCell((short)8);
                c8.setEncoding(HSSFCell.ENCODING_UTF_16);
                c8.setCellValue(m.getDoctor());
                
                HSSFCell c9 = row.createCell((short)9);
                c9.setEncoding(HSSFCell.ENCODING_UTF_16);
                c9.setCellValue(m.getMaster());
                
                HSSFCell c10 = row.createCell((short)10);
                c10.setEncoding(HSSFCell.ENCODING_UTF_16);
                c10.setCellValue(m.getBachelor());
                
                HSSFCell c11 = row.createCell((short)11);
                c11.setEncoding(HSSFCell.ENCODING_UTF_16);
                c11.setCellValue(m.getCollege());
                
                HSSFCell c12 = row.createCell((short)12);
                c12.setEncoding(HSSFCell.ENCODING_UTF_16);
                c12.setCellValue(m.getTotalEmployee());
                
            }
            
            //==========================================================
            
            String filename = "a.xls";//设置下载时客户端Excel的名称    
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
}
